<?php

use Carbon\Carbon;
use App\Utils\Dao;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\Builder;

Builder::macro('timeBetween', function ($column, $timestamp = false, $min_time = null, $max_time = null) {
    $min_time = $min_time ?: I('get.min_time');
    $max_time = $max_time ?: I('get.max_time');

    return $this->when($min_time, function ($q) use ($min_time, $column, $timestamp) {
        if (! $timestamp) {
            $min_time = Carbon::createFromTimestamp($min_time);
        }
        return $q->where($column, '>=', $min_time);
    })
        ->when($max_time, function ($q) use ($max_time, $column, $timestamp) {
            if (! $timestamp) {
                $max_time = Carbon::createFromTimestamp($max_time);
            }
            return $q->where($column, '<=', $max_time);
        });
});

Builder::macro('whenDayRange', function ($day_column, $day_range, $separator = '~') {
    return $this->when($day_range, function($q) use ($day_column, $day_range, $separator) {
        $_ranges  = explode($separator, $day_range);
        return $q->whereBetween($day_column, [
            Carbon::createFromTimestamp(strtotime($_ranges[0]))->format('Y-m-d'),
            Carbon::createFromTimestamp(strtotime($_ranges[1]))->addDay(1)->format('Y-m-d')
        ]);
    });
});

Builder::macro('whenTimeRange', function ($day_column, $day_range, $separator = '~') {
    return $this->when($day_range, function($q) use ($day_column, $day_range, $separator) {
        $_ranges  = explode($separator, $day_range);
        $start_time = trim($_ranges[0]) . ' 0000:00:00';
        $end_time = trim($_ranges[1]) . ' 23:59:59';

        return $q->whereBetween($day_column, [$start_time, $end_time]);
    });
});

/**
 * replace(data, '\\"', "") regexp '"name":"([^"]*)方汉文([^"]*)"'
 * 模糊查询：'%李四%'
 * 精确查询：'李四'
 * ([^"]*) ：匹配除"之外的任意字符串，正则匹配出""中的内容为value
 * 兼容转义中文查询
 *
 * 该方法也有局限性
 * 1.在多维数组中有相同的key，可能会有干扰
 * 例如：
 *   [
 *        'name' => '方汉文01',
 *        'mobile' => '17762745826',
 *        'email' => '877172774@qq.com',
 *        'father' => [
 *            'name' => '方汉文02',
 *            'mobile' => '17762745001',
 *            'email' => '877177001@qq.com',
 *        ],
 *        'mather' => [
 *            'name' => 'fhw002',
 *            'mobile' => '17762745002',
 *            'email' => '877177002@qq.com',
 *        ],
 *   ];
 * 这时你想查询第一层中的name为带有'fhw'时，里面的name可能会干扰
 *
 * 2.无视"符号，因为"是json的终结符，所以查询是会无视key和value的"
 * 例如：
 * [
 *      'name' => '方"汉"文'
 * ]
 * 用方汉文也可以匹配出来
 *
 * 注意：该方法最佳场景应该是，已知key是惟一的，且value不包含"，这时的查询是精确的
 * 例如：
 * [
 *      'address1' => '湖北省武汉市光谷大厦1509',
 *      'address2' => '北京市朝阳区光谷大厦1509'
 * ]
 * 想查询'address1'包含'光谷大厦'，则下面'address2'中的'光谷大厦'不会干扰
 * whereJson('data', 'address1', '%光谷大厦%')
 */
Builder::macro('whereJson', function ($column, $key, $value) {
    // 兼容转义中文查询
    $unicodeValue = '';
    $pattern = '/[^\x00-\x80]/';
    if(preg_match($pattern, $value)){
        $unicodeValue = json_encode($value);
        $unicodeValue = str_replace('"', '', $unicodeValue);
        // 如果想匹配\u，sql必须写成\\\\u，而php字符串又要转义，所以会变成8个\，所以\u变成了\\\\\\\\u
        $unicodeValue = str_replace('\u', "\\\\\\\\u", $unicodeValue);
    }

    $value = str_replace('"', '', $value);

    $value = str_replace('%', '([^"]*)', $value);
    if ($unicodeValue) {
        $unicodeValue = str_replace('%', '([^"]*)', $unicodeValue);

        $valueJson = "((replace({$column}, '\\\\\"', \"\") regexp '\"{$key}\":\"{$value}\"') or (replace({$column}, '\\\\\"', \"\") regexp '\"{$key}\":\"{$unicodeValue}\"'))";
    } else {
        $valueJson = "replace({$column}, '\\\\\"', \"\") regexp '\"{$key}\":\"{$value}\"'";
    }

    return $this->whereRaw($valueJson);
});

/**
 * 局限：
 * 1.查询的key和value中不能含有"，因为"是序列化的'终结符'
 * 例如：
 * [
 *      'name' => '"方汉文"'
 * ]
 * 序列化结构: s:4:"name";s:11:""方汉文""; 这样的结构会导致正则无法匹配出真正的value
 *
 * 2.多维数组中如有相同的key，则有可能会产生干扰，和whereJson一样
 *
 * 注意：该方法最佳场景应该与whereJson一样
 *
 * data regexp '"name";s:([1-9][0-9]*):"([^"]*)方汉文([^"]*)"'
 */
Builder::macro('whereSerialize', function ($column, $key, $value) {
    $slen = '([1-9][0-9]*)';
    if (false !== strpos($value, '%')) {
        $value = str_replace('%', '([^"]*)', $value);
    } else {
        $slen = strlen($value);
    }

    $valueJson = "{$column} regexp '\"{$key}\";s:{$slen}:\"{$value}\";'";

    return $this->whereRaw($valueJson);
});

/**
 * 指定索引
 * 使用场景：mysql无法选择正确的索引导致sql语句很慢，指定索引后有性能有显著提升
 * $model : 查询的model，格式：Donate::class
 * $columnStr : 指定索引的列而不是索引名，顺序不能乱，格式：create_time,project_id,status，也可以是单字段：project_id
 */
Builder::macro('forceIndex', function ($model, $columnStr) {
    $tableSchema = (new $model)->getConnectionName();
    $tableName = (new $model)->getTable();

    $sql = "
        SELECT index_name, group_concat(column_name ORDER BY seq_in_index asc) as 'columns'
        FROM information_schema.statistics
        WHERE table_schema = :table_schema AND table_name = :table_name
        group by index_name
        having columns = :columns
    ";
    $binds = [
        'table_schema' => $tableSchema,
        'table_name' => $tableName,
        'columns' => $columnStr
    ];
    $result = Dao::getRow($tableSchema, $sql, $binds);

    $indexName = array_get($result, 'index_name', '');
    if ($indexName) {
        return $this->from(DB::raw("{$tableName} force index ({$indexName})"));
    } else {
        throw new \Exception("[{$columnStr}]没有索引!");
//        return $this;
    }
});

/**
 * 忽略索引
 * 使用场景：排除一个绝对错误的索引列
 * $model : 查询的model，格式：Donate::class
 * $columnStr : 指定索引的列而不是索引名，顺序不能乱，格式：create_time,project_id,status，也可以是单字段：project_id
 */
Builder::macro('ignoreIndex', function ($model, $columnStr) {
    $tableSchema = (new $model)->getConnectionName();
    $tableName = (new $model)->getTable();

    $sql = "
        SELECT index_name, group_concat(column_name ORDER BY seq_in_index asc) as 'columns'
        FROM information_schema.statistics
        WHERE table_schema = :table_schema AND table_name = :table_name
        group by index_name
        having columns = :columns
    ";
    $binds = [
        'table_schema' => $tableSchema,
        'table_name' => $tableName,
        'columns' => $columnStr
    ];
    $result = Dao::getRow($tableSchema, $sql, $binds);

    $indexName = array_get($result, 'index_name', '');
    if ($indexName) {
        return $this->from(DB::raw("{$tableName} ignore index ({$indexName})"));
    } else {
        return $this;
    }
});

Builder::macro('joinTypeOfTreasure', function ($joinTableName) {
    return $this->join($joinTableName, $joinTableName . '.object_id', '=', 'treasure_of_objects.object_id');
});

Builder::macro('groupByTime', function ($key, $value) {
    return $this->when(! is_bool($value), function ($q) use ($value) {
        return $q->groupBy($value);
    })
        ->when($value === true, function ($q) use ($key) {
            if ($key == 'day') {
                return $q->groupBy('year', 'month', 'day');
            } elseif ($key == 'month') {
                return $q->groupBy('year', 'month');
            } elseif ($key == 'year') {
                return $q->groupBy('year');
            } else {
                throw new Exception('pass a key');
            }
        });
});

Builder::macro('timeWhen', function ($key, $column, $groupBy = false, $field = 'date') {
    if ($column === true) {
        $q = $this->selectYMDDate($key, $field);
    } else {
        $q = $this->substrDate($key, $column, $field);
    }
    return $q->groupByTime($key, $groupBy);
});

Builder::macro('substrDate', function ($key, $column, $field = 'date') {
    if ($key == 'day') {
        return $this->select($this->raw('SUBSTRING('.$column.', 1, 10) as '.$field));
    } elseif ($key == 'month') {
        return $this->select($this->raw('SUBSTRING('.$column.', 1, 7) as '.$field));
    } elseif ($key == 'year') {
        return $this->select($this->raw('SUBSTRING('.$column.', 1, 4) as '.$field));
    } else {
        throw new Exception('pass valid a key');
    }
});

Builder::macro('selectYMDDate', function ($key, $field) {
    if ($key == 'day') {
        return $this->select(['year', 'month', 'day']);
    } elseif ($key == 'month') {
        return $this->select(['year', 'month']);
    } elseif ($key == 'year') {
        return $this->select(['year']);
    } else {
        throw new Exception('pass valid a key');
    }
});

/*
使用案例：
$results = PeriodicBind::ofTeam(TEAM_ID)
    ->selectGroupByDate(['create_time', 'status'], 'month', "count(*) as 'count', sum(donate_amount) as 'amount'")
    ->whereIn('status', ['binded', 'unbind'])
    ->orderBy('create_time', 'desc')
    ->get()
    ->toArray();
输出结果:
[
    [
        "start_date" => "2019-03-01"
        "end_date" => "2019-03-31"
        "status" => "binded"
        "count" => 1,
        "amount" => "0.01"
    ],
    [
        "start_date" => "2019-03-01"
        "end_date" => "2019-03-31"
        "status" => "unbind"
        "count" => 2,
        "amount" => "0.02"
    ],
    [
        "start_date" => "2019-02-01"
        "end_date" => "2019-02-31"
        "status" => "bind"
        "count" => 2,
        "amount" => "0.02"
    ],
    ...
]
大部分情况：->selectGroupByDate(['create_time'], 'month')即可
[
    [
        "start_date" => "2019-03-01"
        "end_date" => "2019-03-31"
        "count" => 1
    ],
    [
        "start_date" => "2019-02-01"
        "end_date" => "2019-02-31"
        "count" => 2
    ],
    [
        "start_date" => "2019-01-01"
        "end_date" => "2019-01-31"
        "count" => 3
    ],
    ...
]

 * $key:
 *      分组的columns,时间必须放在第一个['create_time', 'status']
 *
 * $type:
 *      分组类型:
 *      month : 月
 *      week  : 周
 *      day   : 日
 *
 * $groupDeal:
 *      分组处理：默认为计算数量count(*) as 'count' (可能还有其他，sum(), min(), max()等等)
 * */
Builder::macro('selectGroupByDate', function ($keys, $type, $groupDeal = "count(*) as 'count'") {
    $this->when($keys && $type, function ($q) use ($keys, $type, $groupDeal) {
        $time_column = $keys[0];

        unset($keys[0]);
        $other_column = '';
        if (!empty($keys)) {
            $other_column = implode(',', $keys) . ',';
        }

        if ($type == 'month') {
            $selectRaw = " DATE_FORMAT({$time_column},'%Y-%m-01') as 'start_date', last_day({$time_column}) as 'end_date', {$other_column} {$groupDeal} ";
        } elseif ($type == 'week') {
            $selectRaw = " DATE_FORMAT((SELECT DATE_ADD({$time_column},INTERVAL - WEEKDAY({$time_column}) DAY)), '%Y-%m-%d') as 'start_date', DATE_FORMAT((SELECT DATE_ADD({$time_column},INTERVAL - WEEKDAY({$time_column}) + 6 DAY)),'%Y-%m-%d') as 'end_date', {$other_column} {$groupDeal} ";
        } elseif ($type == 'day') {
            $selectRaw = " DATE_FORMAT({$time_column},'%Y-%m-%d') as 'start_date', DATE_FORMAT({$time_column},'%Y-%m-%d') as 'end_date', {$other_column} {$groupDeal} ";
        }

        if (!empty($keys)) {
            return $q->selectRaw($selectRaw)->groupBy('start_date', implode(',', $keys));
        }

        return $q->selectRaw($selectRaw)->groupBy('start_date');
    });
});

/*
使用案例：
$results = PeriodicBind::ofTeam(TEAM_ID)
    ->selectGroupByMonth(['create_time'])
    ->whereIn('status', ['binded', 'unbind'])
    ->orderBy('create_time', 'desc')
    ->get()
    ->toArray();
结果： start_date:年月日的起始日期，end_date:年月日的结束日期
[
    [
        "start_date" => "2019-03-01"
        "end_date" => "2019-03-31"
        "count" => 1
    ],
    [
        "start_date" => "2019-02-01"
        "end_date" => "2019-02-31"
        "count" => 2
    ],
    [
        "start_date" => "2019-01-01"
        "end_date" => "2019-01-31"
        "count" => 3
    ],
    ...
]
 */
Builder::macro('selectGroupByMonth', function ($keys, $groupDeal = "count(*) as 'count'") {
    return $this->selectGroupByDate($keys, 'month',$groupDeal);
});

/*
 * 与selectGroupByMonth同理
 */
Builder::macro('selectGroupByWeek', function ($keys, $groupDeal = "count(*) as 'count'") {
    return $this->selectGroupByDate($keys, 'week', $groupDeal);
});

/*
 * 与selectGroupByMonth同理
 */
Builder::macro('selectGroupByDay', function ($keys, $groupDeal = "count(*) as 'count'") {
    return $this->selectGroupByDate($keys, 'day', $groupDeal);
});

Builder::macro('pipe', function ($callback) {
    return $callback($this);
});

Builder::macro('sql', function () {
    $bindings = $this->getBindings();
    $sql = str_replace('?', '%s', $this->toSql());
    dd(sprintf($sql, ...$bindings));
});
